/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package FeeDeposit;

import Admission.AdmissionBean;
import Course.CourseBean;
import FeeType.FeeTypeBean;
import Student.StudentBean;
import StudentFeesDetail.StudentFeeDetailsBean;
import java.sql.*;
import java.util.ArrayList;
import java.util.Vector;

/**
 *
 * @author aamod
 */
public class Query {

    static Connection con = null;

    public static int add(FeeDepositBean fdb) throws SQLException {
        int i = 0;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("insert into FEES_DEPOSIT values(FEEDEPOSITID.nextval,?,?,?,?,?,?,?,?,?,?,sysdate,sysdate,?)");
            pre.setInt(1, fdb.getAdmissionId());
            pre.setInt(2, fdb.getSemester());
            pre.setString(3, fdb.getStudSession());
            pre.setInt(4, fdb.getFeeTypeId());
            pre.setFloat(5, fdb.getAmount());
            pre.setInt(6, fdb.getPayMode());
            pre.setString(7, fdb.getChequeNo());
            pre.setString(8, fdb.getBank());
            pre.setString(9, fdb.getBranch());
            pre.setInt(10, fdb.getUserId());
            pre.setString(11, fdb.getReceiptNo());
            i = pre.executeUpdate();
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return i;
    }

    public static Vector viewAll() throws SQLException {
        Vector<Object> list = new Vector<Object>();
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select * from FEES_DEPOSIT fd left join student on student.ADMISSION_ID=fd.ADMISSION_ID left join fee_type on fee_type.fee_type_id=fd.fee_type_id");

            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                FeeDepositBean fdb = new FeeDepositBean();
                FeeTypeBean ftb = new FeeTypeBean();

                fdb.setFeesDepositId(rs.getInt(1));
                fdb.setAdmissionId(rs.getInt(2));
                fdb.setSemester(rs.getInt(3));
                fdb.setStudSession(rs.getString(4));
                fdb.setFeeTypeId(rs.getInt(5));
                fdb.setAmount(rs.getFloat(6));
                fdb.setPayMode(rs.getInt(7));
                fdb.setChequeNo(rs.getString(8));
                fdb.setBank(rs.getString(9));
                fdb.setBranch(rs.getString(10));
                fdb.setUserId(rs.getInt(11));
                fdb.setEntryDate(rs.getDate(12));
                fdb.setDod(rs.getDate(13));
                fdb.setReceiptNo(rs.getString(14));
                fdb.setStuName(rs.getString(17));
                fdb.setFeeType(rs.getString(25));

                ftb.setFeeTypeName(rs.getString("FEE_TYPE_NAME"));
                fdb.setFeeTypeBean(ftb);

                list.add(fdb);
            }

        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return list;
    }

    public static Vector viewAll(String course, int stream, int semester) throws SQLException {
        Vector<Object> list = new Vector<Object>();
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = null;
            if (semester != 0) {

                pre = con.prepareStatement("SELECT * FROM FEES_DEPOSIT fd left join student on student.ADMISSION_ID=fd.ADMISSION_ID left join ADMISSION ad on ad.admission_id=fd.admission_id left join (select fee_type_id,fee_type_name from fee_type) ft on fd.fee_type_id=ft.fee_type_id where ad.course_id =? and fd.semester=?");
                pre.setInt(1, stream);
                pre.setInt(2, semester);
            } else if (stream != 0) {
                pre = con.prepareStatement("SELECT * FROM FEES_DEPOSIT fd left join student on student.ADMISSION_ID=fd.ADMISSION_ID left join ADMISSION ad on ad.admission_id=fd.admission_id left join (select fee_type_id,fee_type_name from fee_type) ft on fd.fee_type_id=ft.fee_type_id where ad.course_id =?");
                pre.setInt(1, stream);
            } else if (course.length() != 0) {
                pre = con.prepareStatement("SELECT * FROM FEES_DEPOSIT fd left join student on student.ADMISSION_ID=fd.ADMISSION_ID left join ADMISSION ad on ad.admission_id=fd.admission_id left join (select fee_type_id,fee_type_name from fee_type) ft on fd.fee_type_id=ft.fee_type_id where ad.course_id in (select course_id from course where course_name=?)");
                pre.setString(1, course);
            } else {
                pre = con.prepareStatement("select * from FEES_DEPOSIT fd left join student on student.ADMISSION_ID=fd.ADMISSION_ID left join fee_type on fee_type.fee_type_id=fd.fee_type_id");
            }

            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                FeeDepositBean fdb = new FeeDepositBean();
                FeeTypeBean ftb = new FeeTypeBean();

                fdb.setFeesDepositId(rs.getInt(1));
                fdb.setAdmissionId(rs.getInt(2));
                fdb.setSemester(rs.getInt(3));
                fdb.setStudSession(rs.getString(4));
                fdb.setFeeTypeId(rs.getInt(5));
                fdb.setAmount(rs.getFloat(6));
                fdb.setPayMode(rs.getInt(7));
                fdb.setChequeNo(rs.getString(8));
                fdb.setBank(rs.getString(9));
                fdb.setBranch(rs.getString(10));
                fdb.setUserId(rs.getInt(11));
                fdb.setEntryDate(rs.getDate(12));
                fdb.setDod(rs.getDate(13));
                fdb.setReceiptNo(rs.getString(14));
                fdb.setStuName(rs.getString(17));
                fdb.setFeeType(rs.getString(25));

                ftb.setFeeTypeName(rs.getString("FEE_TYPE_NAME"));
                fdb.setFeeTypeBean(ftb);
                list.add(fdb);
            }

        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return list;
    }

    public static Vector viewAll(Date from, Date to) throws SQLException {
        Vector<Object> list = new Vector<Object>();
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = null;
            pre = con.prepareStatement("SELECT * FROM FEES_DEPOSIT fd left join (select admission_id,student_name from student) stu on fd.admission_id=stu.admission_id left join (select fee_type_id,fee_type_name from fee_type) ft on fd.fee_type_id=ft.fee_type_id where fd.dod between ? and ? +1");
            pre.setDate(1, from);
            pre.setDate(2, to);

            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                FeeDepositBean fdb = new FeeDepositBean();
                FeeTypeBean ftb = new FeeTypeBean();

                fdb.setFeesDepositId(rs.getInt("FEES_DEPOSIT_ID"));
                fdb.setAdmissionId(rs.getInt("ADMISSION_ID"));
                fdb.setSemester(rs.getInt("SEMESTER"));
                fdb.setStudSession(rs.getString("STUD_SESSION"));
                fdb.setFeeTypeId(rs.getInt("FEE_TYPE_ID"));
                fdb.setAmount(rs.getFloat("AMOUNT"));
                fdb.setPayMode(rs.getInt("PAY_MODE"));
                fdb.setChequeNo(rs.getString("CHEQUE_NO"));
                fdb.setBank(rs.getString("BANK"));
                fdb.setBranch(rs.getString("Branch"));
                fdb.setUserId(rs.getInt("USER_ID"));
                fdb.setEntryDate(rs.getDate("ENTRY_DATE"));
                fdb.setDod(rs.getDate("DOD"));
                fdb.setReceiptNo(rs.getString("RECEIPT_NO"));
                fdb.setStuName(rs.getString("STUDENT_NAME"));
                

                ftb.setFeeTypeName(rs.getString("FEE_TYPE_NAME"));
                fdb.setFeeTypeBean(ftb);
                list.add(fdb);
            }

        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return list;
    }
    public static Vector viewAll(String col,int feetype) throws SQLException {
        Vector<Object> list = new Vector<Object>();
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = null;
            pre = con.prepareStatement("SELECT * FROM FEES_DEPOSIT fd left join (select admission_id,student_name from student) stu on fd.admission_id=stu.admission_id left join (select fee_type_id,fee_type_name from fee_type) ft on fd.fee_type_id=ft.fee_type_id where "+col+"=?");
            pre.setInt(1, feetype);

            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                FeeDepositBean fdb = new FeeDepositBean();
                FeeTypeBean ftb = new FeeTypeBean();

                fdb.setFeesDepositId(rs.getInt("FEES_DEPOSIT_ID"));
                fdb.setAdmissionId(rs.getInt("ADMISSION_ID"));
                fdb.setSemester(rs.getInt("SEMESTER"));
                fdb.setStudSession(rs.getString("STUD_SESSION"));
                fdb.setFeeTypeId(rs.getInt("FEE_TYPE_ID"));
                fdb.setAmount(rs.getFloat("AMOUNT"));
                fdb.setPayMode(rs.getInt("PAY_MODE"));
                fdb.setChequeNo(rs.getString("CHEQUE_NO"));
                fdb.setBank(rs.getString("BANK"));
                fdb.setBranch(rs.getString("Branch"));
                fdb.setUserId(rs.getInt("USER_ID"));
                fdb.setEntryDate(rs.getDate("ENTRY_DATE"));
                fdb.setDod(rs.getDate("DOD"));
                fdb.setReceiptNo(rs.getString("RECEIPT_NO"));
                fdb.setStuName(rs.getString("STUDENT_NAME"));
                

                ftb.setFeeTypeName(rs.getString("FEE_TYPE_NAME"));
                fdb.setFeeTypeBean(ftb);
                list.add(fdb);
            }

        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return list;
    }

    public static Vector viewAllStuFeeDeposit(String email) throws SQLException {
        Vector<Object> list = new Vector<Object>();
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select * from admission a left join FEES_DEPOSIT fd on fd.admission_id=a.admission_id left join fee_type ft on ft.fee_type_id=fd.fee_type_id left join student s on  s.ADMISSION_ID=fd.ADMISSION_ID  where a.STUDENT_EMAIL=?");
            pre.setString(1, email);
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                AdmissionBean ab = new AdmissionBean();
                FeeDepositBean fdb = new FeeDepositBean();
                StudentBean sb = new StudentBean();
                FeeTypeBean ftb = new FeeTypeBean();
                ab.setAddmissionId(rs.getInt(1));
                ab.setStudentEmail(rs.getString(2));
                ab.setStudentMobile(rs.getString(3));
                ab.setRollNumber(rs.getString(4));
                ab.setStudentSession(rs.getString(5));
                ab.setCourseId(rs.getInt(6));
                ab.setApplicationNo(rs.getString(7));
                ab.setAdmissionDate(rs.getDate(8));
                ab.setEntryDate(rs.getDate(9));
                ab.setEntryEmail(rs.getString(10));
                ab.setSemester(rs.getInt(11));
                fdb.setFeesDepositId(rs.getInt(12));
                fdb.setAdmissionId(rs.getInt(13));
                fdb.setSemester(rs.getInt(14));
                fdb.setStudSession(rs.getString(15));
                fdb.setFeeTypeId(rs.getInt(16));
                fdb.setAmount(rs.getFloat(17));
                fdb.setPayMode(rs.getInt(18));
                fdb.setChequeNo(rs.getString(19));
                fdb.setBank(rs.getString(20));
                fdb.setBranch(rs.getString(21));
                fdb.setUserId(rs.getInt(22));
                fdb.setEntryDate(rs.getDate(23));
                fdb.setDod(rs.getDate(24));
                fdb.setReceiptNo(rs.getString(25));
                ftb.setFeeTypeId(rs.getInt(26));
                ftb.setFeeTypeName(rs.getString(27));
                ftb.setEntryDate(rs.getDate(28));
                fdb.setFeeTypeBean(ftb);
                ab.setFeeDepositBean(fdb);
                sb.setStudentId(rs.getInt(29));
                sb.setAddressId(rs.getInt(30));
                sb.setStudentName(rs.getString(31));
                sb.setGender(rs.getString(32));
                sb.setAddressId(rs.getInt(33));
                sb.setEntryDate(rs.getDate(34));
                sb.setCategoryId(rs.getInt(35));
                sb.setPhotoName(rs.getString(36));
                sb.setEntryEmail(rs.getString(37));
                sb.setDob(rs.getDate(38));
                ab.setStudentBean(sb);
                list.add(ab);
            }
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return list;
    }

    public static Vector getFeeReminder(String course, int stream, int sem) throws SQLException {
        Vector<Object> list = new Vector<Object>();
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = null;
            if (course.length() != 0 && stream != 0 && sem != 0) {
                pre = con.prepareStatement("select * from (select ADMISSION_ID,sum(total_amount)total,sum(amount_paid)paid from STUDENT_FEES_DETAIL group by ADMISSION_ID)c left join (select admission_id,student_name from student) ad on c.admission_id=ad.admission_id left join (select admission_id,course_id,student_session,semester from admission)adm on adm.admission_id=c.admission_id left join (select course_id,course_name from course)cour on cour.course_id=adm.course_id where total>paid and cour.course_name=? and cour.course_id=? and adm.semester=?");
                pre.setString(1, course);
                pre.setInt(2, stream);
                pre.setInt(3, sem);
            } else if (course.length() != 0 && stream != 0) {
                pre = con.prepareStatement("select * from (select ADMISSION_ID,sum(total_amount)total,sum(amount_paid)paid from STUDENT_FEES_DETAIL group by ADMISSION_ID)c left join (select admission_id,student_name from student) ad on c.admission_id=ad.admission_id left join (select admission_id,course_id,student_session,semester from admission)adm on adm.admission_id=c.admission_id left join (select course_id,course_name from course)cour on cour.course_id=adm.course_id where total>paid and cour.course_name=? and cour.course_id=?");
                pre.setString(1, course);
                pre.setInt(2, stream);
            } else if (course.length() != 0) {
                pre = con.prepareStatement("select * from (select ADMISSION_ID,sum(total_amount)total,sum(amount_paid)paid from STUDENT_FEES_DETAIL group by ADMISSION_ID)c left join (select admission_id,student_name from student) ad on c.admission_id=ad.admission_id left join (select admission_id,course_id,student_session,semester from admission)adm on adm.admission_id=c.admission_id left join (select course_id,course_name from course)cour on cour.course_id=adm.course_id where total>paid and cour.course_name=?");
                pre.setString(1, course);
            } else {
                pre = con.prepareStatement("select * from (select ADMISSION_ID,sum(total_amount)total,sum(amount_paid)paid from STUDENT_FEES_DETAIL group by ADMISSION_ID)c left join (select admission_id,student_name from student) ad on c.admission_id=ad.admission_id left join (select admission_id,course_id,student_session,semester from admission)adm on adm.admission_id=c.admission_id left join (select course_id,course_name from course)cour on cour.course_id=adm.course_id where total>paid");
            }

            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                StudentBean sb = new StudentBean();
                StudentFeeDetailsBean sfd = new StudentFeeDetailsBean();
                AdmissionBean ab = new AdmissionBean();
                CourseBean cb = new CourseBean();
                sb.setAdmissionId(rs.getInt("ADMISSION_ID"));
                sfd.setAmountPaid(rs.getInt("Paid"));
                sfd.setAmount(rs.getInt("TOTAL"));
                sb.setStudentFeesDetailBean(sfd);
                sb.setStudentName(rs.getString("Student_name"));

                ab.setCourseId(rs.getInt("COURSE_ID"));
                ab.setStudentSession(rs.getString("STUDENT_SESSION"));
                ab.setSemester(rs.getInt("semester"));
                sb.setAdmissionBean(ab);

                cb.setCourseName(rs.getString("COURSE_NAME"));
                cb.setCourseId(rs.getInt("Course_id"));
                ab.setCourseBean(cb);

                list.add(sb);
            }

        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return list;
    }

    public static ArrayList getPaymode() throws SQLException {
        ArrayList list=new ArrayList();
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("SELECT distinct pay_mode FROM FEES_DEPOSIT");

            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
               list.add(rs.getInt("PAY_MODE"));
            }

        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return list;
    }
}
